package com.glela;

import java.io.File;
import java.io.FileOutputStream;
import java.util.List;
import java.util.Map;

import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.collections4.MapUtils;
import org.apache.commons.io.FileUtils;
import org.apache.commons.io.IOUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.glela.common.util.Config;
import com.glela.common.util.FileUtil;

/**
 * 报表excel工具类
 * @author zls
 */
public class ReportExcelUtil {
	private static final String	HTTP_F_XIAOPULAILE_COM_IMAGES_FILE_UPLOAD	= Config.getString("file.upload.url");
	private static final Logger	logger										= LoggerFactory.getLogger(ReportExcelUtil.class);
	private static String		dir;
	private static String		filename;

	public static String createExecl(String full_filename, Map<String, List<Map<String, Object>>> result, Map<String, String[][]> names, Map<String, String> sheetNameMap) {
		if (StringUtils.isBlank(full_filename)) {
			full_filename = File.separator;
		}
		if (!full_filename.startsWith(File.separator)) {
			full_filename = File.separator + full_filename;
		}
		full_filename += "_" + System.currentTimeMillis() + ".xlsx";
		dir = full_filename.substring(0, full_filename.lastIndexOf(File.separator));
		filename = full_filename.substring(full_filename.lastIndexOf(File.separator) + 1, full_filename.length());
		//		File path = new File(dir);
		//		if (!path.exists()) {
		//			path.mkdirs();
		//		}
		// 创建一个webbook，对应一个Excel文件
		//在内存中只保留100行记录,超过100就将之前的存储到磁盘里
		Workbook wb = new SXSSFWorkbook(100);
		//从result中读取每个sheet的内容
		int i = 0;
		for (String key : result.keySet()) {
			// 创建sheet 并生成excel文件
			initSheet(wb, i++, result.get(key), names.get(key), sheetNameMap.get(key));
		}
		// 将文件存到指定位置
		File file = null;
		FileOutputStream fout = null;
		try {
			file = FileUtils.getFile(dir, filename);
			fout = new FileOutputStream(file);
			wb.write(fout);
			//			wb.close();
		} catch (Exception e) {
			logger.error("报表：生成excel异常：{}", e.getMessage(), e);
		} finally {
			IOUtils.closeQuietly(fout);
			fout = null;
		}
		return uploadFile(file);
	}

	private static String uploadFile(File file) {
		String url = null;
		try {
			if (StringUtils.isBlank(HTTP_F_XIAOPULAILE_COM_IMAGES_FILE_UPLOAD)) {
				throw new NullPointerException("文件上传服务期地址为空file.upload.url请配置");
			}
			String postFile = FileUtil.postFile(HTTP_F_XIAOPULAILE_COM_IMAGES_FILE_UPLOAD, file, "xiaopu");
			if (StringUtils.isEmpty(postFile)) {
				throw new Exception("文件服务器无响应结果");
			}

			JSONObject fastJsonToObj = JSONObject.parseObject(postFile);
			if (MapUtils.isEmpty(fastJsonToObj)) {
				throw new Exception("文件服务器响应结果为空");
			}
			if (MapUtils.getBooleanValue(fastJsonToObj, "result")) {//同步成功回写文件访问地址
				JSONArray object = fastJsonToObj.getJSONArray("data");
				if (CollectionUtils.isEmpty(object)) {
					throw new Exception("文件服务器返回文件地址为空");
				} else {
					JSONObject fileMap = object.getJSONObject(0);
					if (MapUtils.getBooleanValue(fileMap, "uploadFlag")) {
						url = MapUtils.getString(fileMap, "filePath");
						//保存文件信息到数据库
						FileUtils.forceDelete(file);
					} else {
						logger.error("上传文件到服务器失败：{}", fileMap);
						throw new Exception("文件服务器保存文件失败：" + MapUtils.getString(fileMap, "message"));
					}
				}
			} else {
				throw new Exception(MapUtils.getString(fastJsonToObj, "message", "上传阿里报表文件失败"));
			}
		} catch (Exception e) {
			logger.error(e.getMessage(), e);
			throw new NullPointerException("同步文件到文件服务器失败");
		}
		return url;
	}

	/**
	 * 生成sheet
	 */
	public static void initSheet(Workbook wb, int sheetNum, List<Map<String, Object>> sheetData, String[][] columnNames, String sheetName) {
		// 创建sheet
		Sheet sheet = wb.createSheet();
		wb.setSheetName(sheetNum, sheetName);
		// 在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
		Row row = sheet.createRow(0);

		//设置字体
		Font font = wb.createFont();
		font.setFontHeightInPoints((short) 12); //字体高度
		//		font.setColor(HSSFFont.COLOR_RED); //字体颜色
		font.setFontName("黑体"); //字体
		font.setBold(true);//宽度

		// 创建单元格，并设置值表头 设置表头居中
		CellStyle style = wb.createCellStyle();
		style.setAlignment(HorizontalAlignment.CENTER_SELECTION); // 创建一个居中格式
		style.setFont(font);
		// 设置第一行标题
		for (int i = 0; i < columnNames.length; i++) {
			Cell cell = row.createCell(i);
			cell.setCellValue(columnNames[i][0]);
			cell.setCellStyle(style);
			//			sheet.autoSizeColumn(i);
			sheet.setColumnWidth(i, columnNames[i][0].getBytes().length * 240);
		}
		// 写入实体数据
		if (CollectionUtils.isNotEmpty(sheetData)) {
			for (int i = 0; i < sheetData.size(); i++) {
				//创建表格，并填装数据
				row = sheet.createRow(i + 1);
				Map<String, Object> map = sheetData.get(i);
				for (int k = 0; k < columnNames.length; k++) {
					Cell createCell = row.createCell(k);
					switch (columnNames[k][2]) {
						case "long"://数字类型
							createCell.setCellValue(MapUtils.getLongValue(map, columnNames[k][1], 0));
							break;
						case "double"://数字类型
							createCell.setCellValue(MapUtils.getDoubleValue(map, columnNames[k][1], 0));
							break;

						default:
							createCell.setCellValue(MapUtils.getString(map, columnNames[k][1], "--"));
							break;
					}
					CellStyle cellStyle = getCellStyle(columnNames[k][3], wb);
					if (cellStyle != null) {
						createCell.setCellStyle(cellStyle);
					}
				}
			}
		}
	}

	public static CellStyle getCellStyle(String key, Workbook wb) {
		CellStyle cellStyle = null;
		DataFormat format = wb.createDataFormat();
		switch (key) {
			case "%":
				cellStyle = wb.createCellStyle();
				cellStyle.setDataFormat(format.getFormat("0.00%"));
				break;
			case "¥":
				cellStyle = wb.createCellStyle();
				cellStyle.setDataFormat(format.getFormat("¥#,##0.00"));
				break;

			default:
				break;
		}
		return cellStyle;
	}

	public static void main(String[] args) {
		File file = FileUtils.getFile("http://f.xiaopulaile.com/xiaopu/2018/4/27/8be7939f-5feb-484d-ad99-6ba380c7fa9c.xlsx");
		if (file != null) {
			System.out.println(file.getPath());
		}
		File file2 = new File("C:/Users/Administrator/AppData/Local/Netease/FlashMail/tmp/M1-13-700/attachs1525658134/image001.png");
		System.out.println(uploadFile(file2));
	}
}